**Housekeeping
local ver_suff _9_21
clear all
cap log close
cd "/Users/`c(username)'/Dropbox/misconduct/do_files`ver_suff'"
log using ../logs`ver_suff'/read_arrests.log, text replace
set more off
set excelxlsxlargefile on

**OFFICERS

**Read first officer sheet (labeled "Export worksheet")
import excel using ../FOIA/FOIA_arrests_05182021/17309-P661045-Arresting-Officers.xlsx, cellrange(A2:G1048002) sheet("Export Worksheet")

save ../processed_data`ver_suff'/arrests_officers/officers_0, replace
clear

**Read subsequent officer sheets (labeled "Sheet1-Sheet9")
forvalues i=1/9{
	import excel ../FOIA/FOIA_arrests_05182021/17309-P661045-Arresting-Officers.xlsx, cellrange(A2:G1048002) sheet("Sheet`i'")
	
	save ../processed_data`ver_suff'/arrests_officers/officers_`i', replace
	clear
}

**Read final officer sheet (labeled "Sheet10")
import excel ../FOIA/FOIA_arrests_05182021/17309-P661045-Arresting-Officers.xlsx, cellrange(A2:G151645) sheet("Sheet10")

**Append all officer sheets together
forvalues i=0/9{
	append using ../processed_data`ver_suff'/arrests_officers/officers_`i'
}

**Name variables
rename A ar_date
rename B cb_no
rename C officer_last_name
rename D officer_first_name
rename E mi
rename F officer_role
rename G appointed_date

**Save unified officer data
save ../processed_data`ver_suff'/arrests_officers/officers_all, replace
clear

**ARRESTS
**Read first arrest sheet (labeled "Arrests Data Set I")
import excel using ../FOIA/FOIA_arrests_05182021/17309-P661045_Arrests_YTD.xlsx, cellrange(A2:W1048002) sheet("Arrests Data Set I")

save ../processed_data`ver_suff'/arrests_officers//arrests_1, replace
clear
*/

**Read subsequent arrest sheets (labeled "Data Set II-Data Set V")
local j = 2
foreach i in II III IV V{
	import excel using ../FOIA/FOIA_arrests_05182021/17309-P661045_Arrests_YTD.xlsx, cellrange(A2:W1048002) sheet("Data Set `i'")
	
	save ../processed_data`ver_suff'/arrests_officers/arrests_`j', replace
	local j = `j' + 1
	clear
}

**Read final arrest sheet (labeled "Data Set VI")
import excel using ../FOIA/FOIA_arrests_05182021/17309-P661045_Arrests_YTD.xlsx, cellrange(A2:W406803) sheet("Data Set VI")

**Append together all arrest sheets
forvalues i = 1/5{
	append using ../processed_data`ver_suff'/arrests_officers/arrests_`i'
}

**Name variables
rename A rd_no
rename B line_no
rename C event_no
rename D arrestee_last
rename E arrestee_first
rename F arrestee_middle
rename G age
rename H sex
rename I race
rename J cb_no
rename K fbi_code
rename L arrest_date_time
rename M arr_address
rename N arr_district
rename O arr_beat
rename P cpd_area
rename Q statute
rename R stat_descr
rename S received_in_lockup
rename T released_from_lockup
rename U bond_type_cd
rename V bont_amt
rename W bond_date

**Save unified arrest data
save ../processed_data`ver_suff'/arrests_officers/arrests_all, replace

clear
log close
